---
title: "cp_claimprofile_get_sc_book stored procedure documentation"
summary: "Analysis of the cp_claimprofile_get_sc_book SQL Server (BDS) stored procedure including business rules and operations."
status: draft
owner: "SAIF Corporation"
last_reviewed: 2026-08-31
source_schema: "eisD0P11.dbo"
source_name: "cp_claimprofile_get_sc_book"
source_type: "PROCEDURE"
retrieval_status: "found"
retrieval_command: "legends sqlserver query --name cp_claimprofile_get_sc_book"
retrieval_date: "2026-08-31"
---

# cp_claimprofile_get_sc_book

## Overview

| Field | Value |
| --- | --- |
| **Schema** | `eisD0P11.dbo` |
| **Type** | SQL Server stored procedure (BDS) |
| **Database** | SQL Server (`eisD0P11`, referred to internally as "BDS") |
| **Status** | ✅ `found` — retrieved via Legends CLI |
| **Query executed** | `legends sqlserver query --name cp_claimprofile_get_sc_book` |
| **Calling libraries** | `SAIF.Business.View.Policy` |
| **Calling classes** | `clsPolicySecurity` |

## Purpose

`cp_claimprofile_get_sc_book` returns the active policy book for a loss-control (safety consultant)
consultant, used by `clsPolicySecurity.GetPolicyListForSafetyConsultant`. It is based on the older
`cp_lc_active_book` procedure per an in-source comment.

## Procedures / Methods

### cp_claimprofile_get_sc_book

| Field | Value |
| --- | --- |
| **Returns** | `POLICY_NAME`, `POLICY_NUMBER` |
| **Called by** | `clsPolicySecurity.GetPolicyListForSafetyConsultant` (`clsPolicySecurity.vb:313-322`) |

**Description:** Supports wildcard matching on the loss-control mail id, excludes flat-cancelled
periods, and lists only active policies with an assigned loss-control consultant (see rules PR-BVP-053
through PR-BVP-055 in [Business Rules](#business-rules) below). A local variable, `@bds_cutoff_dt`, is
assigned from `eis_prod.dbo.fn_policy_cutoff_dt()` but never referenced in the returned select — an
observed dead-local quirk.

#### Parameters

| Parameter | Direction | Type | Default | Description |
| --- | --- | --- | --- | --- |
| `@losscontrol` | IN | `varchar(8)` | — | Loss-control mail id; `%` wildcard supported |

## Business Rules

Rules extracted from this procedure's logic:

| Rule ID | Rule | Condition | Action |
| --- | --- | --- | --- |
| PR-BVP-053 | The loss-control filter supports wildcard matching | `charindex('%', @losscontrol) > 0` | Filter `polTeam.Lss_mail_id LIKE rtrim(@losscontrol)`; otherwise exact match `= @losscontrol` |
| PR-BVP-054 | Flat-cancelled periods are excluded from the safety-consultant book | `T1.PLCY_PD_STAT_CD = 'FC'` | Exclude the row |
| PR-BVP-055 | Only active policies with an assigned loss-control consultant are listed | `wPol.ACTV_PLCY_FL = 'Y'` AND `Lss_mail_id` not null/empty | Include the policy |

> These rules are documented in [business-rules.md](../business-rules.md).

## Operations

Operations and processes implemented in this procedure:

| Operation | Description | Trigger |
| --- | --- | --- |
| List safety-consultant book | Returns the active policy book for a loss-control consultant | `clsPolicySecurity.GetPolicyListForSafetyConsultant` (`clsPolicySecurity.vb:313-322`) |

> These operations are also listed in [operations-processes.md](../operations-processes.md) with source reference back to this document.

## Source Code

<details>
<summary>Transact-SQL stored procedure</summary>

```sql
-- Based on cp_lc_active_book
CREATE PROCEDURE dbo.cp_claimprofile_get_sc_book
    @losscontrol varchar(8)
AS
DECLARE @bds_cutoff_dt datetime
SELECT  @bds_cutoff_dt = eis_prod.dbo.fn_policy_cutoff_dt()   -- declared, not used

SELECT wPol.POLICY_NAME, wPol.POLICY_NUMBER
  FROM ...
 WHERE (   (charindex('%', @losscontrol) > 0
            AND polTeam.Lss_mail_id LIKE rtrim(@losscontrol))
        OR (charindex('%', @losscontrol) = 0
            AND polTeam.Lss_mail_id = @losscontrol))
   AND T1.PLCY_PD_STAT_CD <> 'FC'     -- exclude flat cancel
   AND wPol.ACTV_PLCY_FL = 'Y'
   AND polTeam.Lss_mail_id IS NOT NULL
   AND rtrim(polTeam.Lss_mail_id) <> ''
```

</details>

## Related Documentation
- [Procedure catalog](index.md)
- [Business rules](../business-rules.md)
- [Operations and processes](../operations-processes.md)
- [SQL catalog](../sql/index.md)

## Sources
- SQL Server BDS source: `eisD0P11.dbo.cp_claimprofile_get_sc_book`
- Legends CLI query executed 2026-08-31: `legends sqlserver query --name cp_claimprofile_get_sc_book`
- Calling code: `SAIF.Business.View.Policy/clsPolicySecurity.vb:313-322`